﻿/*########## File: CalendarEventTypes.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'CalendarEventTypes')
  drop table CalendarEventTypes
go

create table CalendarEventTypes
(
  Id int identity(1,1) not null,
  EventName nvarchar(255) not null
)  
go

alter table CalendarEventTypes add constraint PK_CalendarEventTypes_ID primary key(Id) 
go

insert into CalendarEventTypes(EventName) Values('Звонок')
go


go -- Application Delimiter
/*########## File: CalendarEventStatuses.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'CalendarEventStatuses')
  drop table CalendarEventStatuses
go

create table CalendarEventStatuses
(
  Id int identity(1,1) not null,
  StatusName nvarchar(255) not null
)  
go

alter table CalendarEventStatuses add constraint PK_CalendarEventStatuses_ID primary key(Id) 
go

insert into CalendarEventStatuses (StatusName) Values('Новый')
insert into CalendarEventStatuses (StatusName) Values('В процессе')
insert into CalendarEventStatuses (StatusName) Values('Закрыто')
go



go -- Application Delimiter
/*########## File: CalendarEvents.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'CalendarEvents')
  drop table CalendarEvents
go

create table CalendarEvents
(
  Id int identity(1,1) not null,
  EventTypeId int not null,
  AssignedUserId int not null,
  EventText ntext not null,
  IsAutoCreated bit not null,
  CreatedDate datetime not null,
  CreatedUserId int not null,
  EventDate datetime not null,
  CalendarEventStatusId int not null,
  EventTime time not null
)  
go

alter table CalendarEvents add constraint PK_CalendarEvents_ID primary key(Id) 
go

alter table CalendarEvents add constraint FK_CalendarEvents_Users_AssignedUserId 
      foreign key (AssignedUserId) references Users(Id) on delete cascade
go

alter table CalendarEvents add constraint FK_CalendarEvents_Users_CreatedUserId 
      foreign key (CreatedUserId) references Users(Id)
go

alter table CalendarEvents add constraint FK_CalendarEvents_CalendarEventTypes_EventTypeId 
      foreign key (EventTypeId) references CalendarEventTypes(Id) on delete cascade
go

alter table CalendarEvents add constraint FK_CalendarEvents_CalendarEventStatuses_CalendarEventStatusId 
      foreign key (CalendarEventStatusId) references CalendarEventStatuses(Id) 
go


create nonclustered index IX_CalendarEvents_AssignedUserId on CalendarEvents
       (AssignedUserId asc)
go

go -- Application Delimiter
/*########## File: Candidate_Types.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Candidate_Types')
  drop table Candidate_Types
go

create table Candidate_Types
(
  Id int identity(1,1) not null,
  TypeName nvarchar(255) not null
)  
go

alter table Candidate_Types add constraint PK_Candidate_Types_ID primary key(Id) 
go

insert into Candidate_Types(TypeName) Values ('Архивный')
insert into Candidate_Types(TypeName) Values ('Работающий')
insert into Candidate_Types(TypeName) Values ('Стажирующийся')
insert into Candidate_Types(TypeName) Values ('Активный')
go

go -- Application Delimiter
/*########## File: Candidates_Add_TypeId.sql; Comment by owner:  ##########*/
use Dosvid
go

alter table Candidates add CandidateTypeId int null
go

update Candidates set
  CandidateTypeId = 4
go

alter table Candidates alter column CandidateTypeId int not null
go

alter table Candidates add constraint FK_Candidates_Candidate_Types_CandidateTypeId 
      foreign key (CandidateTypeId) references Candidate_Types(Id)
go

go -- Application Delimiter
/*########## File: Candidate_Resume_CREATE_and_Fill_Data.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

/****** Object:  Table [dbo].[Candidate_Resume]    Script Date: 08/10/2011 23:16:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Candidate_Resume](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CandidateID] [int] NOT NULL,
	[Resume] [image] NOT NULL,
 CONSTRAINT [PK_Candidate_Resume] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [dbo].[Candidate_Resume](CandidateID,[Resume])
SELECT Id,[Resume] FROM [dbo].[Candidates]
WHERE [Resume] IS NOT NULL
GO




go -- Application Delimiter
/*########## File: Candidate_Resume_ADD_FK.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

ALTER TABLE [dbo].[Candidate_Resume]  WITH CHECK ADD  CONSTRAINT [FK_Candidate_Resume_Candidates_CandidateID] FOREIGN KEY([CandidateID])
REFERENCES [dbo].[Candidates] ([Id])
GO

ALTER TABLE [dbo].[Candidate_Resume] CHECK CONSTRAINT [FK_Candidate_Resume_Candidates_CandidateID]
GO



go -- Application Delimiter
/*########## File: Candidates_DROP_COLUMN_Resume.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO
ALTER TABLE [dbo].[Candidates]
DROP COLUMN [Resume]


go -- Application Delimiter
/*########## File: Candidates_UTrig.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

/****** Object:  Trigger [dbo].[Candidates_UTrig]    Script Date: 08/14/2011 00:06:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[Candidates_UTrig] ON [dbo].[Candidates] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 
  
  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.ApproveOnPost,-1) <> isnull(d.ApproveOnPost,-1) or
		 isnull(i.ManagerId,0) <> isnull(d.ManagerId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.Territory_CompareImportantID,0) <> isnull(d.Territory_CompareImportantID,0) or
		 isnull(i.SalaryRange_CompareImportantID,0) <> isnull(d.SalaryRange_CompareImportantID,0) or
		 isnull(i.TerritoryID,0) <> isnull(d.TerritoryID,0) or
		 isnull(i.IsCandidate,-1) <> isnull(d.IsCandidate,-1) or
		 isnull(i.IsContactPerson,-1) <> isnull(d.IsContactPerson,-1) or
	     isnull(i.DocumentSum,0) <> isnull(d.DocumentSum,0) or
	     isnull(i.CandidateTypeId,0) <> isnull(d.CandidateTypeId,0) or
		 isnull(d.DateOfBirthDay,'1 jan 2000') <> isnull(i.DateOfBirthDay,'1 jan 2000') or
		 isnull(d.EnterDate,'1 jan 2000') <> isnull(i.EnterDate,'1 jan 2000') or
	     isnull(i.DocumnetNumber,'') <> isnull(d.DocumnetNumber,'') or
		 isnull(i.LastName,'') <> isnull(d.LastName,'') or
		 isnull(i.Adress,'') <> isnull(d.Adress,'') or
		 isnull(i.PrePaid,'') <> isnull(d.PrePaid,'') or
		 isnull(i.Education,'') <> isnull(d.Education,'') or
		 isnull(i.ResumeName,'') <> isnull(d.ResumeName,'') or
		 isnull(i.EducationType,'') <> isnull(d.EducationType,'') or
		 isnull(i.OnlyTradeMarks,'') <> isnull(d.OnlyTradeMarks,'') or
		 update(Characteristic) or
		 update(OtherReqirments)) and
		isnull(c.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))

end



GO



go -- Application Delimiter
/*########## File: SentEmailsHistory.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'SentEmailsHistory')
  drop table SentEmailsHistory
go

create table SentEmailsHistory
(
  Id int identity(1,1) not null,
  CandidatId int not null,
  EMail nvarchar(255) not null,
  SendDate datetime not null
)  
go

alter table SentEmailsHistory add constraint PK_SentEmailsHistory_ID primary key(Id) 
go


alter table SentEmailsHistory add constraint FK_SentEmailsHistory_Candidates_CandidatId 
      foreign key (CandidatId) references Candidates(Id)
go

create nonclustered index IX_SentEmailsHistory_CandidatId on SentEmailsHistory
       (CandidatId asc, EMail asc)
go


go -- Application Delimiter
/*########## File: Vacancy_Types.sql; Comment by owner:  ##########*/
use Dosvid
go

if exists(select 1
		  from sysobjects
		  where name = 'Vacancy_Types')
  drop table Vacancy_Types
go

create table Vacancy_Types
(
  Id int identity(1,1) not null,
  TypeName nvarchar(255) not null
)  
go

alter table Vacancy_Types add constraint PK_Vacancy_Types_ID primary key(Id) 
go

insert into Vacancy_Types(TypeName) Values ('Архивный')
insert into Vacancy_Types(TypeName) Values ('Работающий')
insert into Vacancy_Types(TypeName) Values ('Стажирующийся')
insert into Vacancy_Types(TypeName) Values ('Активный')
go
go -- Application Delimiter
/*########## File: Vacancies_Add_Type.sql; Comment by owner:  ##########*/
use Dosvid
go

alter table Vacancies add VacancyTypeId int null
go

update Vacancies set
  VacancyTypeId = case 
                    when IsArchive = 1 then 1
                    else 4
                  end  
go

alter table Vacancies alter column VacancyTypeId int not null
go

alter table Vacancies add constraint FK_Vacancies_Vacancy_Types_VacancyTypeId 
      foreign key (VacancyTypeId) references Vacancy_Types(Id)
go


go -- Application Delimiter
/*########## File: Vacancies_drop_IsArchive.sql; Comment by owner:  ##########*/
use Dosvid
go

--This constraint can have other name on production DB.
ALTER TABLE [dbo].[Vacancies] DROP CONSTRAINT [DF__Vacancies__IsArc__02084FDA]
go

alter table Vacancies drop column IsArchive
go
go -- Application Delimiter
/*########## File: Vacancies_UTrig.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

/****** Object:  Trigger [dbo].[Vacancies_UTrig]    Script Date: 08/14/2011 00:01:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[Vacancies_UTrig] ON [dbo].[Vacancies] AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 

  update v set
    v.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Vacancies v
  join inserted i on v.Id = i.Id
  join deleted d on d.Id = i.Id
  where (isnull(i.EmployerID,0) <> isnull(d.EmployerID,0) or
		 isnull(i.PositionID,0) <> isnull(d.PositionID,0) or
		 isnull(i.EmployeeWhoFindCandidatesID,0) <> isnull(d.EmployeeWhoFindCandidatesID,0) or
		 isnull(i.EmployeeWhoApproveCandidatesID,0) <> isnull(d.EmployeeWhoApproveCandidatesID,0) or
		 isnull(i.SalaryRangeID,0) <> isnull(d.SalaryRangeID,0) or
		 isnull(i.TerritoryId,0) <> isnull(d.TerritoryId,0) or
		 isnull(i.AgeMin,0) <> isnull(d.AgeMin,0) or
		 isnull(i.AgeMax,0) <> isnull(d.AgeMax,0) or
		 isnull(i.CarID,0) <> isnull(d.CarID,0) or
		 isnull(i.VacancyTypeId,0) <> isnull(d.VacancyTypeId,0) or
		 isnull(i.SexID,0) <> isnull(d.SexID,0) or
		 isnull(i.Car_CompareImportantID,0) <> isnull(d.Car_CompareImportantID,0) or
		 isnull(i.Sex_CompareImportantID,0) <> isnull(d.Sex_CompareImportantID,0) or
		 isnull(i.Age_CompareImportantID,0) <> isnull(d.Age_CompareImportantID,0) or
		 isnull(i.Position_CompareImportantID,0) <> isnull(d.Position_CompareImportantID,0) or
		 isnull(i.ExpirienceYear_CompareImportantID,0) <> isnull(d.ExpirienceYear_CompareImportantID,0) or
		 isnull(d.VacancyDate,'1 jan 2000') <> isnull(i.VacancyDate,'1 jan 2000') or
		 isnull(d.SalaryDate,'1 jan 2000') <> isnull(i.SalaryDate,'1 jan 2000') or
		 isnull(d.TrialPeriod,'1 jan 2000') <> isnull(i.TrialPeriod,'1 jan 2000') or
		 isnull(d.TradeMarks,'') <> isnull(i.TradeMarks,'') or
		 isnull(d.EducationType,'') <> isnull(i.EducationType,'') or
		 isnull(d.SalaryOnTrialPeriod,'') <> isnull(i.SalaryOnTrialPeriod,'') or
		 isnull(d.Education,'') <> isnull(i.Education,'') or
		 update(Comment) or Update(Comment1) or
		 isnull(d.ExpirienceYear,0) <> isnull(i.ExpirienceYear,0)) and
		isnull(v.DLC,dateadd(day,-1,getdate())) <> convert(datetime,convert(varchar,getdate(),102))

END

GO



go -- Application Delimiter
/*########## File: sp_GetCandidatesForVacancies.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetCandidatesForVacancies]    Script Date: 08/14/2011 02:06:36 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_GetCandidatesForVacancies](@VacancyID int) 
AS
BEGIN
  if object_id('TempDB..#SelectedMandatory') is not null 
    drop table #SelectedMandatory
    
  create table #SelectedMandatory(CandidateID int, Value int)
 
  --insert record by mandatory conditions
  insert into #SelectedMandatory(CandidateID,Value)
  select c.Id, 0  
  from Candidates c
  join Vacancies v on v.Id = @VacancyID
  join Employers e on e.Id = v.EmployerID
  where c.CandidateTypeId = 4 and 
	   (((v.Car_CompareImportantID = 1) and (v.CarID = c.CarID)) or
        (isnull(v.Car_CompareImportantID,2) = 2)) 
        and
        (((v.Age_CompareImportantID = 1) and 
          (DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between 
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMin),1) and
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMax),100))) or
         (isnull(v.Age_CompareImportantID,2) = 2))
        and
        (((v.Sex_CompareImportantID = 1) and ((v.SexID = 3) or (v.SexID = c.SexID))) or
         (isnull(v.Sex_CompareImportantID,2) = 2))
        and
        (((v.ExpirienceYear_CompareImportantID = 1) and (v.ExpirienceYear <= (select SUM(ce.Years)	
																			  from Candidat_Expiriences ce
																			  where ce.CandidatID = c.Id and
																				    ce.PositionID = v.PositionID))) or
		 (isnull(v.ExpirienceYear_CompareImportantID,2) = 2))
		and
		((((v.Position_CompareImportantID = 1) or (c.Position_CompareImportantID = 1)) and
		   (exists(select 1
				   from Candidate_Positions p1
				   where p1.CandidateId = c.Id and p1.PositionId = v.PositionID))) or
		 ((isnull(v.Position_CompareImportantID,2) = 2) and (isnull(c.Position_CompareImportantID,2) = 2)))
		and
		(((c.SalaryRange_CompareImportantID = 1) and (c.SalaryRangeID <= v.SalaryRangeID)) or
		 (isnull(c.SalaryRange_CompareImportantID,2) = 2))
		and
		(((c.Territory_CompareImportantID = 1) and 
		   exists(select 1
				  from Candidat_RangeTerritories crt
				  where crt.CandidateId = c.Id and crt.RangeTerritoryId = v.TerritoryId)) or
		 (isnull(c.Territory_CompareImportantID,2) = 2))
		and
		e.Id not in (select cee.EmployerID
					 from Candidat_ExceptEmployers cee
					 where cee.CandidatID = c.Id)
		and 
		(
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id and
			         vcg.CommodityGroupId not in (select cecg.CommodityGroupId
												  from Candidat_ExceptCommodityGroups cecg
												  where cecg.CandidatID = c.Id))
		or
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id
			   having COUNT(1) = 0)
	    )
		 
  -- update record value by passed conditions
  update sm set
    sm.Value = case
			     when v.CarID = c.CarID then 1
				 else 0
			   end +
			   case 
			     when DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between v.AgeMin and v.AgeMax
			       then 1
			     else 0
			   end +
			   case 
			     when (v.SexID = 3) or (v.SexID = c.SexID) then 1
			     else 0
			   end +
			   case 
			     when v.ExpirienceYear <= (select SUM(ce.Years)	
										   from Candidat_Expiriences ce
										   where ce.CandidatID = c.Id and
										         ce.PositionID = v.PositionID) then 1
                 else 0
               end +
               case 
                 when (exists(select 1
				              from Candidate_Positions p1
				              where p1.CandidateId = c.Id and p1.PositionId = v.PositionID)) then 1
                 else 0
               end +
               case 
                 when c.SalaryRangeID <= v.SalaryRangeID then 1
                 else 0
               end +
               case
                 when exists(select 1
				             from Candidat_RangeTerritories crt
				             where crt.CandidateId = c.Id and crt.RangeTerritoryId = v.TerritoryId)
				   then 1
                 else 0
               end
  from #SelectedMandatory sm
  join Candidates c on c.Id = sm.CandidateID
  join Vacancies v on v.Id = @VacancyID
  
  
  --output
  select c.*
  from Candidates c
  join #SelectedMandatory sm on c.Id = sm.CandidateID
  order by sm.Value desc
END

GO



go -- Application Delimiter
/*########## File: sp_GetVacanciesForCandidate.sql; Comment by owner:  ##########*/
USE [Dosvid]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetVacanciesForCandidate]    Script Date: 08/14/2011 02:10:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_GetVacanciesForCandidate](@CandidateID int) 
AS
BEGIN
  if object_id('TempDB..#SelectedMandatory') is not null 
    drop table #SelectedMandatory
    
  create table #SelectedMandatory(VacancyID int, Value int)
 
  --insert record by mandatory conditions
  insert into #SelectedMandatory(VacancyID,Value)
  select v.Id, 0  
  from Vacancies v
  join Employers e on e.Id = v.EmployerID
  join Candidates c on c.Id = @CandidateID
  where v.VacancyTypeId = 4 and
	    (((v.Car_CompareImportantID = 1) and (v.CarID = c.CarID)) or
        (isnull(v.Car_CompareImportantID,2) = 2)) 
        and
        (((v.Age_CompareImportantID = 1) and 
          (DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between 
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMin),1) and
                isnull((select a.AgeName
						from Ages a
						where a.Id = v.AgeMax),100))) or
         (isnull(v.Age_CompareImportantID,2) = 2))
        and
        (((v.Sex_CompareImportantID = 1) and ((v.SexID = 3) or (v.SexID = c.SexID))) or
         (isnull(v.Sex_CompareImportantID,2) = 2))
        and
        (((v.ExpirienceYear_CompareImportantID = 1) and (v.ExpirienceYear <= (select SUM(ce.Years)	
																			  from Candidat_Expiriences ce
																			  where ce.CandidatID = c.Id and
																				    ce.PositionID = v.PositionID))) or
		 (isnull(v.ExpirienceYear_CompareImportantID,2) = 2))
		and
		((((v.Position_CompareImportantID = 1) or (c.Position_CompareImportantID = 1)) and
		   (exists(select 1
				   from Candidate_Positions p1
				   where p1.CandidateId = c.Id and p1.PositionId = v.PositionID))) or	
	     ((isnull(v.Position_CompareImportantID,2) = 2) and (isnull(c.Position_CompareImportantID,2) = 2)))
		and
		(((c.SalaryRange_CompareImportantID = 1) and (c.SalaryRangeID <= v.SalaryRangeID)) or
		 (isnull(c.SalaryRange_CompareImportantID,2) = 2))
		and
		(((c.Territory_CompareImportantID = 1) and 
		  (exists(select 1
				  from Candidat_RangeTerritories crt
				  where crt.CandidateId = c.Id and crt.RangeTerritoryId = v.TerritoryId))) or
		 (isnull(c.Territory_CompareImportantID,2) = 2))
		and
		e.Id not in (select cee.EmployerID
					 from Candidat_ExceptEmployers cee
					 where cee.CandidatID = c.Id)
		and 
		(
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id and
			         vcg.CommodityGroupId not in (select cecg.CommodityGroupId
												  from Candidat_ExceptCommodityGroups cecg
												  where cecg.CandidatID = c.Id))
		or
		exists(select 1
			   from Vacancy_CommodityGroups vcg
			   where vcg.VacancyId = v.Id
			   having COUNT(1) = 0)
	    )
		 
  -- update record value by passed conditions
  update sm set
    sm.Value = case
			     when v.CarID = c.CarID then 1
				 else 0
			   end +
			   case 
			     when DATEDIFF(YEAR,isnull(c.DateOfBirthDay,getdate()),GETDATE()) between v.AgeMin and v.AgeMax
			       then 1
			     else 0
			   end +
			   case 
			     when (v.SexID = 3) or (v.SexID = c.SexID) then 1
			     else 0
			   end +
			   case 
			     when v.ExpirienceYear <= (select SUM(ce.Years)	
										   from Candidat_Expiriences ce
										   where ce.CandidatID = c.Id and
										         ce.PositionID = v.PositionID) then 1
                 else 0
               end +
               case 
                 when (exists(select 1
				              from Candidate_Positions p1
				              where p1.CandidateId = c.Id and p1.PositionId = v.PositionID)) then 1
                 else 0
               end +
               case 
                 when c.SalaryRangeID <= v.SalaryRangeID then 1
                 else 0
               end +
               case
                 when (exists(select 1
				              from Candidat_RangeTerritories crt
				              where crt.CandidateId = c.Id and crt.RangeTerritoryId = v.TerritoryId))
                   then 1
                 else 0
               end
  from #SelectedMandatory sm
  join Vacancies v on v.Id = sm.VacancyID
  join Candidates c on c.Id = @CandidateID
  
  --output
  select v.*
  from Vacancies v
  join #SelectedMandatory sm on v.Id = sm.VacancyID
  order by sm.Value desc
END

GO



go -- Application Delimiter
/*########## File: Candidate_Resume_Triggers.sql; Comment by owner:  ##########*/
use Dosvid
go

create TRIGGER Candidate_Resume_UTrig ON Candidate_Resume AFTER UPDATE
AS 
BEGIN
  if @@rowcount = 0 return	 

  if Update(Resume) begin
    update c set
      c.DLC = convert(datetime,convert(varchar,getdate(),102))
    from Candidates c
    join inserted i on c.Id = i.CandidateID
  end

END
GO


create TRIGGER Candidate_Resume_ITrig ON Candidate_Resume AFTER insert
AS 
BEGIN
  if @@rowcount = 0 return	 

  update c set
    c.DLC = convert(datetime,convert(varchar,getdate(),102))
  from Candidates c
  join inserted i on c.Id = i.CandidateID
END
GO

go -- Application Delimiter
/*########## File: Update_IsContactPerson.sql; Comment by owner:  ##########*/
use Dosvid
go

update c set
 IsContactPerson = 1
from Candidates c
where 
exists(select 1
	   from Employer_ContactPersons cp
	   where cp.CandidatId = c.Id)


go -- Application Delimiter
